from __future__ import annotations

from typing import Optional, Tuple
import re


def _extract_object_names(sql: str) -> Tuple[Optional[str], Optional[str]]:
    """Best-effort extract table and index/constraint names from a SQL snippet.
    Returns (table_name, object_name)
    """
    try:
        up = sql.strip().upper()
        table = None
        obj = None
        # INSERT INTO ... / UPDATE ... / CREATE/ALTER TABLE ...
        m = re.search(r'\bINSERT\s+INTO\s+[`\"]?([A-Za-z0-9_\.]+)[`\"]?', sql, flags=re.IGNORECASE)
        if not m:
            m = re.search(r'\bUPDATE\s+[`\"]?([A-Za-z0-9_\.]+)[`\"]?', sql, flags=re.IGNORECASE)
        if not m:
            m = re.search(r'\bTABLE\s+"?([A-Za-z0-9_\.]+)"?', sql, flags=re.IGNORECASE)
        if m:
            table = m.group(1)
        # INDEX / CONSTRAINT name
        m2 = re.search(r'\b(CONSTRAINT|INDEX|UNIQUE\s+INDEX)\s+"?([A-Za-z0-9_\.]+)"?', sql, flags=re.IGNORECASE)
        if m2:
            obj = m2.group(2)
        return table, obj
    except Exception:
        return None, None


def _extract_column_context(sql: str, start_pos: int, end_pos: int) -> Optional[str]:
    """Extract column definition context around a position in SQL.
    Returns a snippet showing the column definition.
    """
    try:
        # Find the start of the column definition (look backwards for comma or opening paren)
        context_start = max(0, start_pos - 150)
        context_end = min(len(sql), end_pos + 50)
        
        # Extract context
        context = sql[context_start:context_end]
        
        # Try to find column name by looking backwards from the error position
        before = sql[:start_pos]
        # Look for pattern: "column_name" TYPE ... or column_name TYPE ...
        # Search backwards for the column name
        col_match = re.search(r'["`]?([A-Za-z0-9_]+)["`]?\s+[^,)]*$', before, re.IGNORECASE)
        if col_match:
            col_name = col_match.group(1)
            # Extract full column definition from context
            col_def_match = re.search(
                rf'["`]?{re.escape(col_name)}["`]?\s+[^,)]+',
                context,
                re.IGNORECASE
            )
            if col_def_match:
                return col_def_match.group(0).strip()[:200]
        
        # Fallback: return context snippet around the error
        return context.strip()[:200]
    except Exception:
        return None


def diagnose_sql_error(sql: str, error_text: str, phase: str) -> str:
    """
    Return a Chinese diagnostic message with concrete modification advice for KWDB.
    phase: one of 'DDL', 'DML', 'CONSTRAINTS', 'BATCH'.
    """
    phase_cn = {
        'DDL': 'DDL 架构',
        'DML': 'DML INSERT',
        'CONSTRAINTS': '索引/约束',
        'BATCH': '批量执行',
    }.get(phase, phase)

    head = [f"执行{phase_cn}时出错：{error_text.strip()}"]
    table, obj = _extract_object_names(sql or '')
    if table:
        head.append(f"关联对象(表)：{table}")
    if obj:
        head.append(f"关联对象(索引/约束)：{obj}")

    advice: list[str] = []
    et = error_text.lower()
    sql_lower = (sql or '').lower()

    # 通用语法/标识符 - 需要更精准定位
    if 'syntax error' in et or 'at or near' in et:
        advice.append("语法不被 KWDB 接受：")
        
        # 尝试定位具体问题位置
        location_info = []
        
        # 1. 检查错误位置标记（PostgreSQL/CockroachDB 错误信息中的 ^ 标记）
        if '^' in error_text:
            lines = error_text.split('\n')
            for i, line in enumerate(lines):
                if '^' in line:
                    # 找到标记行，尝试获取上下文
                    if i > 0:
                        context_line = lines[i-1].strip()
                        location_info.append(f"- 错误位置: {context_line[:100]}")
                    break
        
        # 2. 检查常见不兼容的数据类型语法
        # MySQL 的 tinyint(1), int(11), varchar(255) 等在 PostgreSQL 中需要去掉括号
        type_patterns = [
            (r'\btinyint\s*\(\s*\d+\s*\)', 'tinyint(n)', 'BOOLEAN 或 SMALLINT'),
            (r'\bsmallint\s*\(\s*\d+\s*\)', 'smallint(n)', 'SMALLINT（去掉括号）'),
            (r'\bint\s*\(\s*\d+\s*\)', 'int(n)', 'INT4 或 INTEGER（去掉括号）'),
            (r'\bbigint\s*\(\s*\d+\s*\)', 'bigint(n)', 'INT8 或 BIGINT（去掉括号）'),
            (r'\bmediumint\s*\(\s*\d+\s*\)', 'mediumint(n)', 'INT4（去掉括号）'),
            (r'\bfloat\s*\(\s*\d+\s*,\s*\d+\s*\)', 'float(m,d)', 'REAL 或 DOUBLE PRECISION'),
            (r'\bdouble\s*\(\s*\d+\s*,\s*\d+\s*\)', 'double(m,d)', 'DOUBLE PRECISION'),
            (r'\bdecimal\s*\(\s*\d+\s*,\s*\d+\s*\)', 'decimal(m,d)', 'DECIMAL(m,d) 或 NUMERIC(m,d)'),
        ]
        
        for pattern, mysql_type, pg_type in type_patterns:
            # Search in original SQL to preserve case and get correct positions
            matches = list(re.finditer(pattern, sql, re.IGNORECASE))
            if matches:
                for match in matches:
                    col_context = _extract_column_context(sql, match.start(), match.end())
                    if col_context:
                        # 提取列名
                        col_name_match = re.search(r'["`]?([A-Za-z0-9_]+)["`]?\s+', col_context, re.IGNORECASE)
                        if col_name_match:
                            col_name = col_name_match.group(1)
                            location_info.append(f"- 列 '{col_name}': 数据类型 {mysql_type} 不符合语法")
                        else:
                            location_info.append(f"- 数据类型 {mysql_type} 不符合语法")
                            location_info.append(f"  位置: {col_context[:100]}")
                    else:
                        location_info.append(f"- 数据类型 {mysql_type} 不符合语法")
        
        # 3. 检查其他常见 MySQL 语法（COMMENT = '...' 是支持的，不检测）
        if 'engine' in sql_lower:
            location_info.append("- 表选项问题: ENGINE = ... 语法不符合语法")
        if 'charset' in sql_lower or 'character set' in sql_lower:
            location_info.append("- 表选项问题: CHARSET/CHARACTER SET 语法不符合语法")
        if 'collate' in sql_lower and 'collate' not in et:  # 避免与列级 COLLATE 混淆
            if '=' in sql_lower:
                location_info.append("- 表选项问题: COLLATE = ... 语法不符合语法")
        
        # 4. 检查 AUTO_INCREMENT（应该已转换为序列，但可能有问题）
        if 'auto_increment' in sql_lower:
            location_info.append("- 列选项问题: AUTO_INCREMENT 不符合语法")
        
        # 5. 如果找到了具体问题，添加到建议中
        if location_info:
            advice.extend(location_info)
        else:
            # 如果没有找到具体问题，只说明有语法错误
            advice.append("- 存在不符合 KWDB 语法的定义")


    # 表/列缺失
    if 'does not exist' in et or 'undefined table' in et or 'undefined column' in et:
        advice.append("引用的表/列不存在：")



    # 重复对象
    if 'already exists' in et or 'duplicate key name' in et:
        advice.append("对象已存在：")
        advice.append("- 为 CREATE 语句添加 IF NOT EXISTS（若语义允许）。")
        advice.append("- 或在创建前先 DROP 同名对象，或改用不同名称。")

    # 非空/默认值/类型不匹配（INSERT 常见）
    if ('null value in column' in et and 'violates not-null' in et) or 'violates not-null' in et:
        advice.append("INSERT 违反 NOT NULL：为该列提供值或在业务允许时去掉 NOT NULL/添加默认值。")
    if 'violates check constraint' in et:
        advice.append("违反 CHECK 约束：调整数据或修改 CHECK 条件以符合目标库约束。")
    if ('invalid input syntax for' in et) or ('cannot cast' in et) or ("doesn't match type" in et) or ('type mismatch' in et):
        # Try to extract column/table and expected/actual types
        col = None
        expect = None
        actual = None
        # Pattern 1: value type int doesn't match type bool of column "b"
        m = re.search(r'value\s+type\s+(\w+)\s+doesn\'?t\s+match\s+type\s+(\w+)\s+of\s+column\s+"?([A-Za-z0-9_]+)"?', error_text, flags=re.IGNORECASE)
        if m:
            actual, expect, col = m.group(1), m.group(2), m.group(3)
        # Pattern 2: column "b" is of type boolean but expression is of type integer
        if not col:
            m = re.search(r'column\s+"?([A-Za-z0-9_]+)"?\s+is\s+of\s+type\s+(\w+)\s+but\s+expression\s+is\s+of\s+type\s+(\w+)', error_text, flags=re.IGNORECASE)
            if m:
                col, expect, actual = m.group(1), m.group(2), m.group(3)
        # Pattern 3: cannot cast type text to integer
        if not expect:
            m = re.search(r'cannot\s+cast\s+(?:type\s+)?(\w+)\s+to\s+(\w+)', error_text, flags=re.IGNORECASE)
            if m:
                actual, expect = m.group(1), m.group(2)

        # Compose specific pointer
        specific = []
        if col:
            specific.append(f"- 列: {col}")
        if table:
            specific.append(f"- 表: {table}")
        if expect or actual:
            specific.append(f"- 类型: 期望 {expect or '?'}，实际 {actual or '?'}")
        if specific:
            advice.append("值类型不匹配：")
            advice.extend(specific)
        else:
            advice.append("值类型不匹配：")


    # 约束/索引相关
    constraint_related = False
    constraint_advice_count_before = len(advice)
    
    # 检测是否是约束/索引相关的错误
    if any(keyword in et for keyword in [
        'constraint', 'index', 'foreign key', 'unique', 'primary key',
        'referential', 'violates', 'violation', 'duplicate key'
    ]):
        constraint_related = True
    
    # 匹配具体的约束/索引错误规则
    if 'duplicate key value violates unique constraint' in et:
        advice.append("唯一约束冲突：")
    elif 'cannot create index concurrently' in et or ('concurrently' in et and 'index' in et):
        advice.append("索引并发创建不支持：")
    elif 'requires an existing index' in et or 'foreign key requires an existing index' in et:
        advice.append("外键需要索引：")
    elif 'referential integrity' in et or ('foreign key' in et and 'violates' in et):
        advice.append("外键约束问题：")
    elif 'constraint' in et and ('violates' in et or 'violation' in et):
        advice.append("约束违反：")
    elif 'index' in et and ('already exists' in et or 'duplicate' in et):
        advice.append("索引已存在：")
    
    # 如果是约束/索引相关错误但没有匹配到具体规则，输出错误语句和系统诊断
    constraint_advice_count_after = len(advice)
    if constraint_related and constraint_advice_count_after == constraint_advice_count_before:
        advice.append("约束/索引执行错误：")
        # 提取错误语句
        snippet = (sql or '').strip().replace('\n', ' ')
        if len(snippet) > 300:
            snippet = snippet[:300] + '...'
        advice.append(f"错误语句: {snippet}")
        advice.append(f"系统错误: {error_text.strip()}")



    # 权限/连接/事务
    if 'permission denied' in et:
        advice.append("权限不足：使用具备 DDL/DML 权限的账号或授予需要的权限。")
    if 'server closed the connection' in et or 'connection refused' in et or 'connection reset' in et:
        advice.append("连接问题：检查 KWDB 服务是否可达、端口/账号/密码/sslmode 设置是否正确，稍后重试。")

    # 存储过程/触发器/引擎差异
    if 'engine' in et or 'row format' in et or 'charset' in et:
        advice.append("MySQL 表选项不被支持：ENGINE/ROW_FORMAT/CHARSET 请在转换时移除或替换为 KWDB 语义。")

    # 无特别命中规则时的兜底
    if not advice:
        # 如果是约束/索引阶段，输出错误语句和系统诊断
        if phase == 'CONSTRAINTS':
            advice.append("约束/索引执行错误：")
            snippet = (sql or '').strip().replace('\n', ' ')
            if len(snippet) > 300:
                snippet = snippet[:300] + '...'
            advice.append(f"错误语句: {snippet}")
            advice.append(f"系统错误: {error_text.strip()}")
        else:
            advice.append("请根据报错定位对应的转换规则或目标库限制进行修改。")

    snippet = (sql or '').strip().replace('\n', ' ')
    if len(snippet) > 300:
        snippet = snippet[:300] + '...'
    
    # 如果已经有错误语句在 advice 中，就不重复添加失败语句片段
    has_error_stmt = any('错误语句:' in a or '失败语句' in a for a in advice)
    detail = [*head, "建议修改:"] + [f"  - {a}" for a in advice]
    if not has_error_stmt:
        detail.append(f"失败语句片段: {snippet}")
    return "\n".join(detail)


def diagnose_system_error(error_text: str, context: str = "") -> str:
    """Diagnostics for system prerequisites like Kafka/SSL."""
    msgs: list[str] = [f"系统环境出错：{error_text.strip()}"]
    ctx = context.lower()
    et = error_text.lower()
    advice: list[str] = []

    # Kafka 未开启/不可达
    if 'kafka' in ctx or 'kafka' in et or 'brokers' in et:
        advice.append("Kafka 不可用：")
        advice.append("- 确认 broker 已启动并监听正确地址，advertised.listeners 可被当前主机访问。")
        advice.append("- 如使用外网或容器，检查防火墙/端口映射。")



    if not advice:
        advice.append("请根据日志检查依赖服务是否已启动并可达，或补齐缺失依赖。")

    msgs.append("建议处理:")
    msgs.extend([f"  - {a}" for a in advice])
    return "\n".join(msgs)

